package birs.controller.expanditure.task;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import birs.model.expanditure.XPD003Model;
import birs.model.expanditure.XPD003ModelA;
import birs.model.expanditure.XPD003ModelB;
import birs.model.expanditure.XPD003ModelC;
import birs.model.expanditure.XPD003ModelD;
import birs.model.expanditure.XPD003ModelE;
import birs.util.ConnectionDB;
import birs.util.ConvertDataType;
import birs.util.ResultSetMapper;

public class XPD003Task {
	
	public void getExpanditureStructure(HttpServletRequest request, HttpServletResponse response) throws IOException{
		double currentBudget = 0.0;
		double paySum = 0.0;
		//get Expenditure by Year
		List<XPD003Model> xpd003ModelList = getExpanditureByYear();
		
		String periodYear = "(";
		for (XPD003Model xpd003Model : xpd003ModelList) {
			periodYear += xpd003Model.getName() +",";
		}
		
		//prepare query string get all year
		periodYear = periodYear.substring(0, periodYear.lastIndexOf(",")) + ")";
		
		//get Expenditure by strategic
		List<XPD003ModelA> xpd003ModelAList = getExpanditureByStategic(periodYear);
		
		String strategyId = "(";
		//set children first level (Children budget by year)
		for (XPD003Model xpd003Model : xpd003ModelList) {
			List<XPD003ModelA> xpd003ModelAListTmp = new ArrayList<XPD003ModelA>();
			for (XPD003ModelA xpd003ModelA : xpd003ModelAList) {
				if(xpd003Model.getName() == xpd003ModelA.getBudgetPeriodId()){
					xpd003ModelAListTmp.add(xpd003ModelA);
					strategyId += xpd003ModelA.getStrategyId() +",";
				}
			}
			currentBudget += xpd003Model.getCurrentBudget();
			paySum += xpd003Model.getPaySum();
			xpd003Model.setChildren(xpd003ModelAListTmp);
		}
		
		//prepare query string get all strategy
		strategyId = strategyId.substring(0,strategyId.lastIndexOf(",")) + ")";
		
		String tacticsId = "(";
		
		//get Expanditure by Tactics
		List<XPD003ModelB> xpd003ModelBList = getExpanditureByTactics(periodYear, strategyId);
		
		for (XPD003ModelA xpd003ModelA : xpd003ModelAList) {
			List<XPD003ModelB> xpd003ModelBListTmp = new ArrayList<XPD003ModelB>();
			for (XPD003ModelB xpd003ModelB : xpd003ModelBList) {
				if((xpd003ModelA.getBudgetPeriodId() == xpd003ModelB.getBudgetPeriodId()) 
						&& (xpd003ModelA.getStrategyId() == xpd003ModelB.getStrategyId())){
					xpd003ModelBListTmp.add(xpd003ModelB);
					tacticsId += xpd003ModelB.getTacticsId() +",";
				}
			}
			xpd003ModelA.setChildren(xpd003ModelBListTmp);
		}
		
		//prepare query string get all tactics
		tacticsId = tacticsId.substring(0,tacticsId.lastIndexOf(",")) + ")";
		
		String productId = "(";
		
		//get Expanditure by product
		List<XPD003ModelC> xpd003ModelCList = getExpanditureByProduct(periodYear, strategyId, tacticsId);
		
		for (XPD003ModelB xpd003ModelB : xpd003ModelBList) {
			List<XPD003ModelC> xpd003ModelCListTmp = new ArrayList<XPD003ModelC>();
			for (XPD003ModelC xpd003ModelC : xpd003ModelCList) {
				if((xpd003ModelB.getBudgetPeriodId() == xpd003ModelC.getBudgetPeriodId()) 
						&& (xpd003ModelB.getStrategyId() == xpd003ModelB.getStrategyId()) 
						&& (xpd003ModelB.getTacticsId() == xpd003ModelC.getTacticsid())){
					xpd003ModelCListTmp.add(xpd003ModelC);
					productId += xpd003ModelC.getProductId() +",";
				}
			}
			xpd003ModelB.setChildren(xpd003ModelCListTmp);
		}
		
		productId = productId.substring(0,productId.lastIndexOf(",")) + ")";
		
		//get Expanditure by project
		List<XPD003ModelD> xpd003ModelDList = getExpanditureByProject(periodYear, strategyId, tacticsId, productId);
		for (XPD003ModelC xpd003ModelC : xpd003ModelCList) {
			List<XPD003ModelD> xpd003ModelDListTmp = new ArrayList<XPD003ModelD>();
			for (XPD003ModelD xpd003ModelD : xpd003ModelDList) {
				if((xpd003ModelC.getBudgetPeriodId() == xpd003ModelD.getBudgetPeriodId()) 
						&& (xpd003ModelC.getStrategyId() == xpd003ModelD.getStrategyId()) 
						&& (xpd003ModelC.getTacticsid() == xpd003ModelD.getTacticsid()) 
						&& (xpd003ModelC.getProductId() == xpd003ModelD.getProductId())){
					xpd003ModelDListTmp.add(xpd003ModelD);
				}
			}
			if(xpd003ModelDListTmp.size()>0){
				xpd003ModelC.setChildren(xpd003ModelDListTmp);
			}else{
				xpd003ModelC.setChildren(null);
			}
		}
		
		XPD003ModelE xpd003ModelE = new XPD003ModelE();
		xpd003ModelE.setName("งบประมาณ");
		xpd003ModelE.setCurrentBudget(currentBudget);
		xpd003ModelE.setPaySum(paySum);
		xpd003ModelE.setChildren(xpd003ModelList);
		
		String resultJson = ConvertDataType.getInstance().objectToJasonArray(xpd003ModelE);
		sendResponse(request, response, resultJson);
		
	}
	
	public List<XPD003Model> getExpanditureByYear(){
		List<XPD003Model> xpd003ModelList = null;
		try(Connection con = ConnectionDB.getMisConnection()){
			ResultSetMapper<XPD003Model> xpd003Model = new ResultSetMapper<XPD003Model>();
			xpd003ModelList = xpd003Model.mapRersultSetToObject(getExpanditureByYearSQL(con), XPD003Model.class);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return xpd003ModelList;
	}
	
	public List<XPD003ModelA> getExpanditureByStategic(String periodYear){
		List<XPD003ModelA> xpd003ModelAList = null;
		try (Connection con = ConnectionDB.getMisConnection()){
			ResultSetMapper<XPD003ModelA> xpd003ModelA = new ResultSetMapper<XPD003ModelA>();
			xpd003ModelAList = xpd003ModelA.mapRersultSetToObject(getExpanditureByStategicSQL(con, periodYear), XPD003ModelA.class);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return xpd003ModelAList;
	}
	
	public List<XPD003ModelB> getExpanditureByTactics(String periodYear, String strategyId){
		List<XPD003ModelB> xpd003ModelBList = null;
		try (Connection con = ConnectionDB.getMisConnection()){
			ResultSetMapper<XPD003ModelB> xpd003ModelB = new ResultSetMapper<XPD003ModelB>();
			xpd003ModelBList = xpd003ModelB.mapRersultSetToObject(getExpanditureByTacticSQL(con, periodYear, strategyId), XPD003ModelB.class);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return xpd003ModelBList;
	}
	
	public List<XPD003ModelC> getExpanditureByProduct(String periodYear, String strategyId, String tacticsId){
		List<XPD003ModelC> xpd003ModelCList = null;
		try (Connection con = ConnectionDB.getMisConnection()){
			ResultSetMapper<XPD003ModelC> xpd003ModelC = new ResultSetMapper<XPD003ModelC>();
			xpd003ModelCList = xpd003ModelC.mapRersultSetToObject(getExpanditureByProductSQL(con, periodYear, strategyId, tacticsId), XPD003ModelC.class);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return xpd003ModelCList;
	}
	
	public List<XPD003ModelD> getExpanditureByProject(String periodYear, String strategyId, String tacticsId, String productId){
		List<XPD003ModelD> xpd003ModelDList = null;
		try (Connection con = ConnectionDB.getMisConnection()){
			ResultSetMapper<XPD003ModelD> xpd003ModelD = new ResultSetMapper<XPD003ModelD>();
			xpd003ModelDList = xpd003ModelD.mapRersultSetToObject(getExpanditureByProjectSQL(con, periodYear, strategyId, tacticsId, productId), XPD003ModelD.class);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return xpd003ModelDList;
	}
	
	
	private void sendResponse(HttpServletRequest request,HttpServletResponse response,String resultJson) throws IOException{
		response.setCharacterEncoding("utf-8");
		response.setContentType("application/json;charset=utf-8");
		PrintWriter out = response.getWriter();
		out.write(resultJson);
		out.close();
	}
	
	private ResultSet getExpanditureByYearSQL(Connection con){
		String sql =" 	SELECT L.BUDGETPERIODID	"
				+" 	,SUM(L.CURRENTBUDGET) AS CURRENTBUDGET	"
				+" 	,SUM(L.PAYSUM) AS PAYSUM	"
				+" 	FROM V_BUD_LINKTYPE L	"
				+" 	WHERE L.BUDGETPERIODID <= (SELECT TO_CHAR(SYSDATE, 'yyyy', 'NLS_CALENDAR=''THAI BUDDHA'' NLS_DATE_LANGUAGE=THAI') AS YEAR FROM DUAL)"
				+" 	AND L.BUDGETPERIODID <> 2550	"
				+"  AND L.BUDGETSTUCTYPE = 'C' "
				+" 	GROUP BY L.BUDGETPERIODID	"
				+" 	ORDER BY L.BUDGETPERIODID";

		return getData(sql, con);
	}
	
	private ResultSet getExpanditureByStategicSQL(Connection con,String periodYear){
		String sql = " 	SELECT L.BUDGETPERIODID "
				+" 	,BG.STRATEGYID 	"
				+" 	, BG.STRATEGYDESCRIPTION 	"
				+" 	, SUM(L.CURRENTBUDGET) AS CURRENTBUDGET 	"
				+" 	, SUM(L.PAYSUM) AS PAYSUM 	"
				+" 	FROM V_BUD_LINKTYPE L 	"
				+" 	, BUDGET B 	"
				+" 	, BUDGETTACTICS BT 	"
				+" 	, BUDGETSTRATEGY BG 	"
				+" 	WHERE L.BUDGETPERIODID IN "+periodYear
				+"  AND L.BUDGETSTUCTYPE = 'C' "
				+" 	AND L.BUDGETID = B.BUDGETID	"
				+" 	AND B.TACTICSID = BT.TACTICSID	"
				+" 	AND BT.STRATEGYID = BG.STRATEGYID	"
				+" 	GROUP BY L.BUDGETPERIODID, BG.STRATEGYID , BG.STRATEGYDESCRIPTION	"
				+" 	ORDER BY CURRENTBUDGET DESC";
		return getData(sql, con);
	}
	
	private ResultSet getExpanditureByTacticSQL(Connection con, String periodYear, String strategyId){
		String sql = " 	SELECT L.BUDGETPERIODID	"
				+" 	, BT.STRATEGYID 	"
				+" 	, BT.TACTICSID	"
				+" 	, BT.TACTICSDESCRIPTION 	"
				+" 	, SUM(L.CURRENTBUDGET) AS CURRENTBUDGET	"
				+" 	, SUM(L.PAYSUM) AS PAYSUM	"
				+" 	FROM V_BUD_LINKTYPE L , BUDGET B , BUDGETTACTICS BT	"
				+" 	WHERE L.BUDGETPERIODID IN "+periodYear
				+" 	AND L.BUDGETSTUCTYPE = 'C'	"
				+" 	AND L.BUDGETID = B.BUDGETID	"
				+" 	AND B.TACTICSID = BT.TACTICSID	"
				+" 	AND BT.STRATEGYID IN "+strategyId
				+" 	GROUP BY L.BUDGETPERIODID, BT.STRATEGYID ,BT.TACTICSID, BT.TACTICSDESCRIPTION	"
				+" 	ORDER BY CURRENTBUDGET DESC";

		return getData(sql, con);
	}
	
	private ResultSet getExpanditureByProductSQL(Connection con, String periodYear, String strategyId, String tacticsId){
		String sql = "SELECT VBL.BUDGETPERIODID	"
				+" 	, BSPLAN.STRATEGYID	"
				+" 	, BSPITEM.TACTICSID	"
				+" 	, VBL.PRODUCTID	"
				+" 	, VBL.PRODUCTNAME	"
				+" 	, SUM(VBL.CURRENTBUDGET) AS CURRENTBUDGET	"
				+" 	, SUM(VBL.PAYSUM) AS PAYSUM	"
				+" 	FROM V_BUD_LINKTYPE VBL	"
				+" 	, BUDGETSTRUCPLAN BSPLAN	"
				+" 	, BUDGETSTRUCPRODUCTITEM BSPITEM	"
				+" 	WHERE VBL.BUDGETPERIODID IN "+periodYear
				+" 	AND VBL.BUDGETSTUCTYPE = 'C'	"
				+" 	AND VBL.PLANID = BSPLAN.PLANID	"
				+" 	AND BSPLAN.STRATEGYID IN "+strategyId
				+" 	AND BSPLAN.BUDGETPERIODID IN "+periodYear
				+" 	AND BSPLAN.BUDGETSTRUCPLANID = BSPITEM.BUDGETSTRUCPLANID	"
				+" 	AND VBL.PRODUCTID = BSPITEM.PRODUCTID	"
				+" 	AND BSPITEM.TACTICSID IN "+tacticsId
				+" 	GROUP BY VBL.BUDGETPERIODID	"
				+" 	, BSPLAN.STRATEGYID	"
				+" 	, BSPITEM.TACTICSID	"
				+" 	, VBL.PRODUCTID	"
				+" 	, VBL.PRODUCTNAME	"
				+" 	ORDER BY CURRENTBUDGET DESC ";

		return getData(sql, con);
	}
	
	private ResultSet getExpanditureByProjectSQL(Connection con, String periodYear, String strategyId, String tacticsId, String productId){
		String sql = " SELECT VBL.BUDGETPERIODID "
				+" 	, VBL.BUDGETGROUPID	"
				+"  , BSPLAN.STRATEGYID "
				+"  ,BSPITEM.TACTICSID "
				+" 	, VBL.PRODUCTID	"
				+" 	, VBL.PROJECTID	"
				+" 	, VBL.PROJECTNAME	"
				+" 	, SUM(VBL.CURRENTBUDGET) AS CURRENTBUDGET	"
				+" 	, SUM(VBL.PAYSUM) AS PAYSUM	"
				+" 	FROM V_BUD_LINKTYPE VBL	"
				+" 	, BUDGETSTRUCPLAN BSPLAN	"
				+" 	, BUDGETSTRUCPRODUCTITEM BSPITEM	"
				+" 	, BUDGETSTRUCPROJECT BSPROJECT	"
				+" 	WHERE VBL.BUDGETPERIODID IN "+periodYear
				+" 	AND VBL.PLANID = BSPLAN.PLANID	"
				+" 	AND BSPLAN.STRATEGYID IN "+strategyId
				+" 	AND BSPLAN.BUDGETSTUCTYPE = 'C'	"
				+" 	AND BSPLAN.BUDGETPERIODID IN "+periodYear
				+" 	AND BSPLAN.BUDGETSTRUCPLANID = BSPITEM.BUDGETSTRUCPLANID	"
				+" 	AND VBL.PRODUCTID = BSPITEM.PRODUCTID	"
				+" 	AND VBL.PRODUCTID IN "+productId
				+" 	AND BSPITEM.TACTICSID IN "+tacticsId
				+" 	AND BSPLAN.BUDGETSTRUCPLANID = BSPROJECT.BUDGETSTRUCPLANID	"
				+" 	AND VBL.PROJECTID = BSPROJECT.PROJECTID	"
				+" 	GROUP BY VBL.BUDGETPERIODID	"
				+" 	, VBL.BUDGETGROUPID	"
				+"  , BSPLAN.STRATEGYID "
				+"  , BSPITEM.TACTICSID "
				+" 	, VBL.PLANID	"
				+" 	, VBL.PRODUCTID	"
				+" 	, VBL.PROJECTID	"
				+" 	, VBL.PROJECTNAME	"
				+" 	ORDER BY CURRENTBUDGET DESC ";
		return getData(sql, con);
	}
	
	private ResultSet getData(String sql,Connection con){
		ResultSet result = null;
		try {
			Statement statement = con.createStatement();
			result = statement.executeQuery(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}
}
